package com.qingcheng.dao;

import com.qingcheng.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportMapper extends Mapper<CategoryReport> {

    @Select("SELECT" +
            " oi.category_id1 categoryId1," +
            " oi.category_id2 categoryId2," +
            " oi.category_id3 categoryId3," +
            " SUM(oi.num) num," +
            " SUM(oi.pay_money) money," +
            " DATE_FORMAT(od.pay_time,'%Y-%m-%d') countDate" +
            " FROM" +
            " tb_order od,tb_order_item oi" +
            " WHERE" +
            " od.id=oi.order_id" +
            " AND od.pay_status='1'" +
            " AND DATE_FORMAT(od.pay_time,'%Y-%m-%d')=#{date}" +
            " GROUP BY" + " oi.category_id1, oi.category_id2, oi.category_id3,DATE_FORMAT(od.pay_time,\"%Y-%m-%d\")")
    public List<CategoryReport> categoryReport(@Param("date") LocalDate date);

    @Select("SELECT" +
            " tc.`category_id1` categoryId1," +
            " vc.`name` categoryName," +
            " SUM(tc.num) num," +
            " SUM(tc.money) money" +
            " FROM" +
            " tb_category_report tc,v_category vc" +
            " WHERE" +
            " tc.`category_id1`=vc.`id`" +
            " AND" +
            " tc.`count_date`" +
            " BETWEEN #{date1} AND #{date2}" +
            " GROUP BY" +
            " tc.`category_id1`," +
            " vc.`name`")
    public List<Map> category1Count(@Param("date1") String date1, @Param("date2") String date2);
}
